package com.css.zfzx.sjcj.modules.yhdisasiterschedule.service.impl;

import com.css.zfzx.sjcj.common.StatisticsEnum;
import com.css.zfzx.sjcj.common.division.service.MyDivisionService;
import com.css.zfzx.sjcj.common.utils.CheckObjFields;
import com.css.zfzx.sjcj.common.vo.DivisionVo;
import com.css.zfzx.sjcj.modules.jobschedule.entity.vo.QueryDataVO;
import com.css.zfzx.sjcj.modules.jobschedule.entity.vo.QueryRespondVO;
import com.css.zfzx.sjcj.modules.pojo.PcStatisticsFillEntity;
import com.css.zfzx.sjcj.modules.sys.usersInfo.UsersVo;
import com.css.zfzx.sjcj.modules.yhdisasiterschedule.dao.pg.RiskcensusDao;
import com.css.zfzx.sjcj.modules.yhdisasiterschedule.dao.primary.YhDisasterDao;
import com.css.zfzx.sjcj.modules.yhdisasiterschedule.service.YhScheduleService;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigInteger;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @Description: pc-statistics
 * @Author: lhl
 * @CreateDate: 2021/8/31 11:22
 */
@Log4j2
@Service
public class YhScheduleServiceImpl implements YhScheduleService {
    private final YhDisasterDao yhDisasterDao;
    private final RiskcensusDao riskcensusDao;
    private final MyDivisionService myDivisionService;

    @Autowired
    public YhScheduleServiceImpl(YhDisasterDao yhDisasterDao,
                                 RiskcensusDao riskcensusDao,
                                 MyDivisionService myDivisionService) {
        this.yhDisasterDao = yhDisasterDao;
        this.riskcensusDao = riskcensusDao;
        this.myDivisionService = myDivisionService;
    }

    /**
     * 库里所有省级区划的集合
     * DivisionVo：
     * provinceCode 省级编码
     * provinceName 省级名称
     */
    public static List<DivisionVo> provinceList = new ArrayList<>();
    /**
     * 库里所有试点县区划的集合
     */
    public static List<DivisionVo> pioneerList = new ArrayList<>();
    /**
     * 所有菜单的集合
     * A:城镇住宅     B:城镇非住宅       C:农村集合住宅     D:农村独立住宅
     * E:农村非住宅		F:市政桥梁		G:供水厂
     * <p>
     * 住宅	大中小学校舍	医疗卫生设施	商业中心	社会服务保障设施	市政桥梁	供水厂	其他
     */
    public static String[] codes = new String[]{"A", "B", "C", "D", "E", "F", "G"};
    public static String[] names = new String[]{"住宅", "大中小学校舍", "医疗卫生设施", "商业中心", "社会服务保障设施", "市政桥梁", "供水厂", "其他"};
    /**
     * 为了计算各菜单所有省的数据和
     * map1:中间库
     * map2:业务库
     */
    public static Map<Integer, Integer> map1 = new HashMap<>();
    public static Map<Integer, Integer> map2 = new HashMap<>();

    @PostConstruct
    public void init() {
        provinceList = myDivisionService.getAllProvince();
        pioneerList = myDivisionService.findPioneerType();
    }

    @Override
    public List<QueryRespondVO> findPcSchedule1(String userId) {
        List<Object[]> countList1 = yhDisasterDao.count1(1);
        List<Object[]> countList2 = riskcensusDao.count(1, 1);
        List<Object[]> countList3 = riskcensusDao.count(2, 1);
        List<Object[]> countList4 = riskcensusDao.count(3, 1);
        List<Object[]> countList5 = riskcensusDao.count(4, 1);
        List<Object[]> countList6 = riskcensusDao.count(5, 1);
        List<Object[]> countList7 = riskcensusDao.count(6, 1);
        List<Object[]> countList8 = riskcensusDao.count(7, 1);
        List<QueryRespondVO> queryRespondVOS = new ArrayList<>();
        for (DivisionVo divisionVo : provinceList) {
            String provinceName = divisionVo.getProvinceName();
            String provinceCode = divisionVo.getProvinceCode();
            QueryRespondVO queryRespondVO = new QueryRespondVO();
            queryRespondVO.setDivisionName(provinceName);
            queryRespondVO.setDivisionCode(provinceCode);
            List<QueryDataVO> queryDataVOS = new ArrayList<>();
            for (String code : codes) {
                QueryDataVO queryDataVO = new QueryDataVO();
                queryDataVO.setTaskCode(code);
                String taskName = null;
                String percentage = null;
                if ("A".equals(code)) {
                    taskName = "城镇住宅";
                    percentage = getQuantity(provinceCode, null, countList2);
                } else if ("B".equals(code)) {
                    taskName = "城镇非住宅";
                    percentage = getQuantity(provinceCode, null, countList3);
                } else if ("C".equals(code)) {
                    taskName = "农村集合住宅";
                    percentage = getQuantity(provinceCode, null, countList4);
                } else if ("D".equals(code)) {
                    taskName = "农村独立住宅";
                    percentage = getQuantity(provinceCode, null, countList5);
                } else if ("E".equals(code)) {
                    taskName = "农村非住宅";
                    percentage = getQuantity(provinceCode, null, countList6);
                } else if ("F".equals(code)) {
                    taskName = "市政桥梁";
                    percentage = getQuantity(provinceCode, null, countList7);
                } else if ("G".equals(code)) {
                    taskName = "供水厂";
                    percentage = getQuantity(provinceCode, null, countList8);
                }
                queryDataVO.setTaskName(taskName);
                queryDataVO.setQuantity(getQuantity(provinceName, code, countList1));//业务库
                queryDataVO.setPercentage(percentage);//中间库
                queryDataVOS.add(queryDataVO);
            }
            queryRespondVO.setDataVOS(queryDataVOS);
            queryRespondVOS.add(queryRespondVO);
        }
        return queryRespondVOS;
    }

    @Override
    public List<QueryRespondVO> findPcSchedule2(String userId) {
        List<Object[]> countList1 = yhDisasterDao.count2(1);
        List<QueryRespondVO> queryRespondVOS = new ArrayList<>();
        for (DivisionVo divisionVo : provinceList) {
            QueryRespondVO queryRespondVO = new QueryRespondVO();
            queryRespondVO.setDivisionName(divisionVo.getProvinceName());
            queryRespondVO.setDivisionCode(divisionVo.getProvinceCode());
            List<QueryDataVO> queryDataVOS = new ArrayList<>();
            for (String name : names) {
                QueryDataVO queryDataVO = new QueryDataVO();
                queryDataVO.setTaskName(name);
                queryDataVO.setQuantity(getQuantity(divisionVo.getProvinceName(), name, countList1));//业务库
                queryDataVOS.add(queryDataVO);
            }
            queryRespondVO.setDataVOS(queryDataVOS);
            queryRespondVOS.add(queryRespondVO);
        }
        return queryRespondVOS;
    }

    @Override
    public List<QueryRespondVO> findPcSchedule3(String userId) {
        List<Object[]> countList1 = yhDisasterDao.count1(2);
        List<Object[]> countList2 = riskcensusDao.count(1, 2);
        List<Object[]> countList3 = riskcensusDao.count(2, 2);
        List<Object[]> countList4 = riskcensusDao.count(3, 2);
        List<Object[]> countList5 = riskcensusDao.count(4, 2);
        List<Object[]> countList6 = riskcensusDao.count(5, 2);
        List<Object[]> countList7 = riskcensusDao.count(6, 2);
        List<Object[]> countList8 = riskcensusDao.count(7, 2);
        List<QueryRespondVO> queryRespondVOS = new ArrayList<>();
        for (DivisionVo divisionVo : pioneerList) {
            String provinceName = divisionVo.getProvinceName();
            String provinceCode = divisionVo.getProvinceCode();
            String cityName = divisionVo.getCityName();
            String areaName = divisionVo.getAreaName();
            String pioneerType = divisionVo.getPioneerType();
            QueryRespondVO queryRespondVO = new QueryRespondVO();
            if ("中华人民共和国".equals(provinceName)) {
                queryRespondVO.setDivisionName(cityName + "/" + areaName);
            } else {
                queryRespondVO.setDivisionName(provinceName + "/" + cityName + "/" + areaName);
            }
            queryRespondVO.setDivisionCode(provinceCode);
            queryRespondVO.setPioneerType(pioneerType);
            List<QueryDataVO> queryDataVOS = new ArrayList<>();
            for (String code : codes) {
                QueryDataVO queryDataVO = new QueryDataVO();
                queryDataVO.setTaskCode(code);
                String taskName = null;
                String percentage = null;
                if ("A".equals(code)) {
                    taskName = "城镇住宅";
                    percentage = getQuantity(provinceCode, null, countList2);
                } else if ("B".equals(code)) {
                    taskName = "城镇非住宅";
                    percentage = getQuantity(provinceCode, null, countList3);
                } else if ("C".equals(code)) {
                    taskName = "农村集合住宅";
                    percentage = getQuantity(provinceCode, null, countList4);
                } else if ("D".equals(code)) {
                    taskName = "农村独立住宅";
                    percentage = getQuantity(provinceCode, null, countList5);
                } else if ("E".equals(code)) {
                    taskName = "农村非住宅";
                    percentage = getQuantity(provinceCode, null, countList6);
                } else if ("F".equals(code)) {
                    taskName = "市政桥梁";
                    percentage = getQuantity(provinceCode, null, countList7);
                } else if ("G".equals(code)) {
                    taskName = "供水厂";
                    percentage = getQuantity(provinceCode, null, countList8);
                }
                queryDataVO.setTaskName(taskName);
                queryDataVO.setQuantity(getQuantity(areaName, code, countList1));//业务库
                queryDataVO.setPercentage(percentage);//中间库
                queryDataVOS.add(queryDataVO);
            }
            queryRespondVO.setDataVOS(queryDataVOS);
            queryRespondVOS.add(queryRespondVO);
        }
        return queryRespondVOS;
    }

    @Override
    public List<QueryRespondVO> findPcSchedule4(String userId) {
        List<Object[]> countList1 = yhDisasterDao.count2(2);
        List<QueryRespondVO> queryRespondVOS = new ArrayList<>();
        for (DivisionVo divisionVo : pioneerList) {
            String provinceName = divisionVo.getProvinceName();
            String provinceCode = divisionVo.getProvinceCode();
            String cityName = divisionVo.getCityName();
            String areaName = divisionVo.getAreaName();
            String pioneerType = divisionVo.getPioneerType();
            QueryRespondVO queryRespondVO = new QueryRespondVO();
            if ("中华人民共和国".equals(provinceName)) {
                queryRespondVO.setDivisionName(cityName + "/" + areaName);
            } else {
                queryRespondVO.setDivisionName(provinceName + "/" + cityName + "/" + areaName);
            }
            queryRespondVO.setDivisionCode(provinceCode);
            queryRespondVO.setPioneerType(pioneerType);
            List<QueryDataVO> queryDataVOS = new ArrayList<>();
            for (String name : names) {
                QueryDataVO queryDataVO = new QueryDataVO();
                queryDataVO.setTaskName(name);
                queryDataVO.setQuantity(getQuantity(areaName, name, countList1));//业务库
                queryDataVOS.add(queryDataVO);
            }
            queryRespondVO.setDataVOS(queryDataVOS);
            queryRespondVOS.add(queryRespondVO);
        }
        return queryRespondVOS;
    }

    //"城镇住宅", "城镇非住宅","农村集合住宅", "农村独立住宅","农村非住宅", "市政桥梁","供水厂"
    //"住宅", "大中小学校舍","医疗卫生设施", "商业中心","社会服务保障设施", "市政桥梁","供水厂","其他"
    public String getQuantity(String province, String code, List<Object[]> countList) {
        BigInteger count = null;
        try {
            for (Object[] objects : countList) {
                String province1 = (String) objects[0];
                if (province.equals(province1)) {
                    if (StringUtils.isEmpty(code)) {
                        count = (BigInteger) objects[1];
                        return String.valueOf(count);
                    } else {
                        String name = objects[1].toString();
                        if (code.equals(name)) {
                            count = (BigInteger) objects[2];
                            return String.valueOf(count);
                        }
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            log.error("字符强转失败！");
        }
        return null;
    }

    @Override
    public void exportPcSchedule1(String userId, HttpServletResponse resp) {
        try {
            String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            String name = dateStr + "数据统计.xlsx";
            //resp.setContentType("application/octet-stream ");
            resp.setHeader("content-Type", "application/vnd.ms-excel");
            resp.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            //resp.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            resp.setHeader("Content-Disposition", "attachment;fileName="
                    + new String(name.getBytes("GB2312"), "ISO8859-1"));
            resp.setCharacterEncoding("UTF-8");

            HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
            // 样式设置
            HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
            columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
            columnHeadStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 背景色AQUA.getIndex()
            columnHeadStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            columnHeadStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            columnHeadStyle.setBorderTop(BorderStyle.THIN);// 上边框
            columnHeadStyle.setBorderRight(BorderStyle.THIN);// 右边框
            columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
            columnHeadStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);// 上下居中
            columnHeadStyle.setWrapText(true);//自动换行

            HSSFSheet sheet = workbook.createSheet("数据统计");
            String[] sheetHeadTitle = new String[]{"城镇住宅", "城镇非住宅", "农村集合住宅", "农村独立住宅", "农村非住宅", "市政桥梁", "供水厂", "合计"};
            //ConvertUtils.register(new DateConverter(null), java.util.Date.class);
            List<Object[]> countList1 = yhDisasterDao.count1(1);
            List<Object[]> countList2 = riskcensusDao.count(1, 1);
            List<Object[]> countList3 = riskcensusDao.count(2, 1);
            List<Object[]> countList4 = riskcensusDao.count(3, 1);
            List<Object[]> countList5 = riskcensusDao.count(4, 1);
            List<Object[]> countList6 = riskcensusDao.count(5, 1);
            List<Object[]> countList7 = riskcensusDao.count(6, 1);
            List<Object[]> countList8 = riskcensusDao.count(7, 1);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            map1 = initMap(map1,0);
            map2 = initMap(map2,0);
            for (int i = 0; i < provinceList.size() + 3; i++) {
                HSSFRow row0 = sheet.createRow(i);
                row0.setHeightInPoints(30);
                int k = 0;
                int sum1 = 0;
                int sum2 = 0;
                for (int t = 0; t < sheetHeadTitle.length * 2 + 1; t++) {
                    HSSFCell cel0_i = row0.createCell(t);
                    if (i == 0) {
                        if (t != 0&&t<sheetHeadTitle.length+1) {
                            sheet.addMergedRegion(new CellRangeAddress(0, 0, 2 * t - 1, 2 * t));
                        } else if(t==0){
                            cel0_i.setCellValue(new HSSFRichTextString("省份"));
                        }
                        if ((t + 1) % 2 == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString(sheetHeadTitle[k]));
                            k++;
                        }
                    } else if (i == 1 && t > 0) {
                        if ((t + 1) % 2 == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString("中间库"));
                        } else {
                            cel0_i.setCellValue(new HSSFRichTextString("业务库"));
                        }
                    } else if (i > 1 && i < provinceList.size() + 2) {
                        String provinceName = provinceList.get(i - 2).getProvinceName();
                        String provinceCode = provinceList.get(i - 2).getProvinceCode();
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString(provinceName));
                        } else if (t > sheetHeadTitle.length * 2 - 2) {
                            if ((t + 1) % 2 == 0) {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(sum1)));
                            } else {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(sum2)));
                            }
                        } else {
                            if ((t + 1) % 2 == 0) {
                                String percentage = null;
                                switch (k) {
                                    case 0:
                                        percentage = getQuantity(provinceCode, null, countList2);
                                        break;
                                    case 1:
                                        percentage = getQuantity(provinceCode, null, countList3);
                                        break;
                                    case 2:
                                        percentage = getQuantity(provinceCode, null, countList4);
                                        break;
                                    case 3:
                                        percentage = getQuantity(provinceCode, null, countList5);
                                        break;
                                    case 4:
                                        percentage = getQuantity(provinceCode, null, countList6);
                                        break;
                                    case 5:
                                        percentage = getQuantity(provinceCode, null, countList7);
                                        break;
                                    case 6:
                                        percentage = getQuantity(provinceCode, null, countList8);
                                        break;
                                }
                                percentage = percentage == null || "null".equals(percentage) ? "0" : percentage;
                                map1.put(k, map1.get(k) + Integer.parseInt(percentage));
                                sum1 = sum1 + Integer.parseInt(percentage);
                                cel0_i.setCellValue(new HSSFRichTextString(percentage));
                            } else {
                                String quantity = getQuantity(provinceName, codes[k], countList1);
                                quantity = quantity == null || "null".equals(quantity) ? "0" : quantity;
                                map2.put(k, map2.get(k) + Integer.parseInt(quantity));
                                sum2 = sum2 + Integer.parseInt(quantity);
                                cel0_i.setCellValue(new HSSFRichTextString(quantity));
                                k++;
                            }
                        }
                    } else if (i == provinceList.size() + 2) {
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString("合计"));
                        } else {
                            if ((t + 1) % 2 == 0) {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(map1.get(k))));
                            } else {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(map2.get(k))));
                            }
                        }
                    }
                    cel0_i.setCellStyle(columnHeadStyle);
                    //sheet.autoSizeColumn((short) t);//宽度自适应
                }
                sheet.setColumnWidth(i, 4000);
            }
            OutputStream out = resp.getOutputStream();
            workbook.write(out);
            // ByteArrayOutputStream bos = new ByteArrayOutputStream();
            // workbook.write(bos);
            // bos.writeTo(out);
            if (out != null) {
                out.flush();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void exportPcSchedule2(String userId, HttpServletResponse resp) {
        try {
            String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            String name = dateStr + "数据统计.xlsx";
            //resp.setContentType("application/octet-stream ");
            resp.setHeader("content-Type", "application/vnd.ms-excel");
            resp.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            //resp.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            resp.setHeader("Content-Disposition", "attachment;fileName="
                    + new String(name.getBytes("GB2312"), "ISO8859-1"));
            resp.setCharacterEncoding("UTF-8");

            HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
            // 样式设置
            HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
            columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
            columnHeadStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 背景色AQUA.getIndex()
            columnHeadStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            columnHeadStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            columnHeadStyle.setBorderTop(BorderStyle.THIN);// 上边框
            columnHeadStyle.setBorderRight(BorderStyle.THIN);// 右边框
            columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
            columnHeadStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);// 上下居中
            columnHeadStyle.setWrapText(true);//自动换行

            HSSFSheet sheet = workbook.createSheet("数据统计");
            String[] sheetHeadTitle = new String[]{"省份", "住宅", "大中小学校舍", "医疗卫生设施", "商业中心", "社会服务保障设施", "市政桥梁", "供水厂", "其他",
                    "合计"};
            //ConvertUtils.register(new DateConverter(null), java.util.Date.class);
            List<Object[]> countList1 = yhDisasterDao.count2(1);
            map1 = initMap(map1,1);
            for (int i = 0; i < provinceList.size() + 2; i++) {
                HSSFRow row0 = sheet.createRow(i);
                row0.setHeightInPoints(30);
                int sum1 = 0;
                for (int t = 0; t < sheetHeadTitle.length; t++) {
                    HSSFCell cel0_i = row0.createCell(t);
                    if (i == 0) {
                        cel0_i.setCellValue(new HSSFRichTextString(sheetHeadTitle[t]));
                    }else if (i == provinceList.size() + 1) {
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString("合计"));
                        } else {
                            cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(map1.get(t-1))));
                        }
                    } else {
                        String provinceName = provinceList.get(i - 1).getProvinceName();
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString(provinceName));
                        } else if (t == sheetHeadTitle.length - 1) {
                            cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(sum1)));
                        }else {
                            String quantity = getQuantity(provinceName, names[t-1], countList1);
                            quantity=quantity==null||"null".equals(quantity)?"0":quantity;
                            sum1=sum1+Integer.valueOf(quantity);
                            map1.put(t-1,map1.get(t-1)+Integer.valueOf(quantity));
                            cel0_i.setCellValue(new HSSFRichTextString(quantity));
                        }
                    }
                    cel0_i.setCellStyle(columnHeadStyle);
                    //sheet.autoSizeColumn((short) t);//宽度自适应
                }
                sheet.setColumnWidth(i, 4000);
            }
            OutputStream out = resp.getOutputStream();
            workbook.write(out);
            // ByteArrayOutputStream bos = new ByteArrayOutputStream();
            // workbook.write(bos);
            // bos.writeTo(out);
            if (out != null) {
                out.flush();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void exportPcSchedule3(String userId, HttpServletResponse resp) {
        try {
            String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            String name = dateStr + "数据统计.xlsx";
            //resp.setContentType("application/octet-stream ");
            resp.setHeader("content-Type", "application/vnd.ms-excel");
            resp.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            //resp.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            resp.setHeader("Content-Disposition", "attachment;fileName="
                    + new String(name.getBytes("GB2312"), "ISO8859-1"));
            resp.setCharacterEncoding("UTF-8");

            HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
            // 样式设置
            HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
            columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
            columnHeadStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 背景色AQUA.getIndex()
            columnHeadStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            columnHeadStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            columnHeadStyle.setBorderTop(BorderStyle.THIN);// 上边框
            columnHeadStyle.setBorderRight(BorderStyle.THIN);// 右边框
            columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
            columnHeadStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);// 上下居中
            columnHeadStyle.setWrapText(true);//自动换行

            HSSFSheet sheet = workbook.createSheet("数据统计");
            String[] sheetHeadTitle = new String[]{"城镇住宅", "城镇非住宅", "农村集合住宅", "农村独立住宅", "农村非住宅", "市政桥梁", "供水厂", "合计"};
            //ConvertUtils.register(new DateConverter(null), java.util.Date.class);
            List<Object[]> countList1 = yhDisasterDao.count1(2);
            List<Object[]> countList2 = riskcensusDao.count(1, 2);
            List<Object[]> countList3 = riskcensusDao.count(2, 2);
            List<Object[]> countList4 = riskcensusDao.count(3, 2);
            List<Object[]> countList5 = riskcensusDao.count(4, 2);
            List<Object[]> countList6 = riskcensusDao.count(5, 2);
            List<Object[]> countList7 = riskcensusDao.count(6, 2);
            List<Object[]> countList8 = riskcensusDao.count(7, 2);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            map1 = initMap(map1,0);
            map2 = initMap(map2,0);
            for (int i = 0; i < pioneerList.size() + 3; i++) {
                HSSFRow row0 = sheet.createRow(i);
                row0.setHeightInPoints(30);
                int k = 0;
                int sum1 = 0;
                int sum2 = 0;
                for (int t = 0; t < sheetHeadTitle.length * 2 + 1; t++) {
                    HSSFCell cel0_i = row0.createCell(t);
                    if (i == 0) {
                        if (t != 0&&t<sheetHeadTitle.length+1) {
                            sheet.addMergedRegion(new CellRangeAddress(0, 0, 2 * t - 1, 2 * t));
                        } else if(t==0){
                            cel0_i.setCellValue(new HSSFRichTextString("省份"));
                        }
                        if ((t + 1) % 2 == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString(sheetHeadTitle[k]));
                            k++;
                        }
                    } else if (i == 1 && t > 0) {
                        if ((t + 1) % 2 == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString("中间库"));
                        } else {
                            cel0_i.setCellValue(new HSSFRichTextString("业务库"));
                        }
                    } else if (i > 1 && i < pioneerList.size() + 2) {
                        String areaName = pioneerList.get(i - 2).getAreaName();
                        String provinceCode = pioneerList.get(i - 2).getProvinceCode();
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString(areaName));
                        } else if (t > sheetHeadTitle.length * 2 - 2) {
                            if ((t + 1) % 2 == 0) {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(sum1)));
                            } else {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(sum2)));
                            }
                        } else {
                            if ((t + 1) % 2 == 0) {
                                String percentage = null;
                                switch (k) {
                                    case 0:
                                        percentage = getQuantity(provinceCode, null, countList2);
                                        break;
                                    case 1:
                                        percentage = getQuantity(provinceCode, null, countList3);
                                        break;
                                    case 2:
                                        percentage = getQuantity(provinceCode, null, countList4);
                                        break;
                                    case 3:
                                        percentage = getQuantity(provinceCode, null, countList5);
                                        break;
                                    case 4:
                                        percentage = getQuantity(provinceCode, null, countList6);
                                        break;
                                    case 5:
                                        percentage = getQuantity(provinceCode, null, countList7);
                                        break;
                                    case 6:
                                        percentage = getQuantity(provinceCode, null, countList8);
                                        break;
                                }
                                percentage = percentage == null || "null".equals(percentage) ? "0" : percentage;
                                map1.put(k, map1.get(k) + Integer.parseInt(percentage));
                                sum1 = sum1 + Integer.parseInt(percentage);
                                cel0_i.setCellValue(new HSSFRichTextString(percentage));
                            } else {
                                String quantity = getQuantity(areaName, codes[k], countList1);
                                quantity = quantity == null || "null".equals(quantity) ? "0" : quantity;
                                map2.put(k, map2.get(k) + Integer.parseInt(quantity));
                                sum2 = sum2 + Integer.parseInt(quantity);
                                cel0_i.setCellValue(new HSSFRichTextString(quantity));
                                k++;
                            }
                        }
                    } else if (i == pioneerList.size() + 2) {
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString("合计"));
                        } else {
                            if ((t + 1) % 2 == 0) {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(map1.get(k))));
                            } else {
                                cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(map2.get(k))));
                            }
                        }
                    }
                    cel0_i.setCellStyle(columnHeadStyle);
                    //sheet.autoSizeColumn((short) t);//宽度自适应
                }
                sheet.setColumnWidth(i, 4000);
            }
            OutputStream out = resp.getOutputStream();
            workbook.write(out);
            // ByteArrayOutputStream bos = new ByteArrayOutputStream();
            // workbook.write(bos);
            // bos.writeTo(out);
            if (out != null) {
                out.flush();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void exportPcSchedule4(String userId, HttpServletResponse resp) {
        try {
            String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            String name = dateStr + "数据统计.xlsx";
            //resp.setContentType("application/octet-stream ");
            resp.setHeader("content-Type", "application/vnd.ms-excel");
            resp.setHeader("Connection", "close"); // 表示不能用浏览器直接打开
            //resp.setHeader("Accept-Ranges", "bytes");// 告诉客户端允许断点续传多线程连接下载
            resp.setHeader("Content-Disposition", "attachment;fileName="
                    + new String(name.getBytes("GB2312"), "ISO8859-1"));
            resp.setCharacterEncoding("UTF-8");

            HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
            // 样式设置
            HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
            columnHeadStyle.setFillPattern(FillPatternType.NO_FILL); // 背景
            columnHeadStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 背景色AQUA.getIndex()
            columnHeadStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            columnHeadStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            columnHeadStyle.setBorderTop(BorderStyle.THIN);// 上边框
            columnHeadStyle.setBorderRight(BorderStyle.THIN);// 右边框
            columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
            columnHeadStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);// 上下居中
            columnHeadStyle.setWrapText(true);//自动换行

            HSSFSheet sheet = workbook.createSheet("数据统计");
            String[] sheetHeadTitle = new String[]{"省份", "住宅", "大中小学校舍", "医疗卫生设施", "商业中心", "社会服务保障设施", "市政桥梁", "供水厂", "其他",
                    "合计"};
            //ConvertUtils.register(new DateConverter(null), java.util.Date.class);
            List<Object[]> countList1 = yhDisasterDao.count2(2);
            map1 = initMap(map1,1);
            for (int i = 0; i < pioneerList.size() + 2; i++) {
                HSSFRow row0 = sheet.createRow(i);
                row0.setHeightInPoints(30);
                int sum1 = 0;
                for (int t = 0; t < sheetHeadTitle.length; t++) {
                    HSSFCell cel0_i = row0.createCell(t);
                    if (i == 0) {
                        cel0_i.setCellValue(new HSSFRichTextString(sheetHeadTitle[t]));
                    }else if (i == pioneerList.size() + 1) {
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString("合计"));
                        } else {
                            cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(map1.get(t-1))));
                        }
                    } else {
                        String areaName = pioneerList.get(i - 1).getAreaName();
                        if (t == 0) {
                            cel0_i.setCellValue(new HSSFRichTextString(areaName));
                        } else if (t == sheetHeadTitle.length - 1) {
                            cel0_i.setCellValue(new HSSFRichTextString(String.valueOf(sum1)));
                        }else {
                            String quantity = getQuantity(areaName, names[t-1], countList1);
                            quantity=quantity==null||"null".equals(quantity)?"0":quantity;
                            sum1=sum1+Integer.valueOf(quantity);
                            map1.put(t-1,map1.get(t-1)+Integer.valueOf(quantity));
                            cel0_i.setCellValue(new HSSFRichTextString(quantity));
                        }
                    }
                    cel0_i.setCellStyle(columnHeadStyle);
                    //sheet.autoSizeColumn((short) t);//宽度自适应
                }
                sheet.setColumnWidth(i, 4000);
            }
            OutputStream out = resp.getOutputStream();
            workbook.write(out);
            // ByteArrayOutputStream bos = new ByteArrayOutputStream();
            // workbook.write(bos);
            // bos.writeTo(out);
            if (out != null) {
                out.flush();
                out.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Map<Integer, Integer> initMap(Map<Integer, Integer> map,int identify) {
        map.put(0, 0);
        map.put(1, 0);
        map.put(2, 0);
        map.put(3, 0);
        map.put(4, 0);
        map.put(5, 0);
        map.put(6, 0);
        if(identify==1){
            map.put(7, 0);
        }
        return map;
    }
}
